package com.hrp.common;


import com.alibaba.druid.pool.DruidDataSource;
import com.hrp.config.RadmDataSourceConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.ResultSetMetaData;  
import java.sql.SQLException;  
import java.util.ArrayList;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;

/**
 * Created by Administrator on 2018/9/23 0023.
 */
public class JdbcUtils {


    private static Logger logger = LoggerFactory.getLogger(RadmDataSourceConfig.class);

    private static final String USERNAME = "sa";
    //数据库密码
    private static final String PASSWORD = "123";
    //驱动信息
    private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    //数据库地址
    //private static final String URL = "jdbc:sqlserver://222.222.22.196\\HIS;databasename=THIS4";
    private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=UI_MMS";
    private static Connection connection;
    private static PreparedStatement pstmt;
    private static ResultSet resultSet;

    private static Integer initialSize = 10;
    private static Integer minIdle = 5;
    private static Integer maxActive = 50;
    //# 配置获取连接等待超时的时间
    private static Integer maxWait = 60000;
    // # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    private static Integer timeBetweenEvictionRunsMillis = 2000;
    //# 配置一个连接在池中最小生存的时间，单位是毫秒
    private static Integer minEvictableIdleTimeMillis = 300000;
    //private String validationQuery=SELECT 1 FROM DUAL
    private static boolean testWhileIdle = true;
    private static boolean testOnBorrow = false;
    private static boolean testOnReturn = false;
    private static DruidDataSource dataSource = null;


    // # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    private static String filters = "stat,wall,log4j";
    private boolean logSlowSql = true;

    //DruidDataSource dataSource=new DruidDataSource();;
    static {

        dataSource = new DruidDataSource();
        dataSource.setDriverClassName(DRIVER);
        dataSource.setUrl(URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);


        dataSource.setInitialSize(initialSize);
        dataSource.setMinIdle(minIdle);
        dataSource.setMaxActive(maxActive);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        //dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);

        try {
            dataSource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter", e);
        }
    }


    /**
     * 增加、删除、改
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {
        boolean flag = false;
        int result = -1;

        //pstmt = connection.prepareStatement(sql);
        pstmt = dataSource.getConnection().prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        flag = result > 0 ? true : false;
        return flag;
    }

    /**
     * 查询单条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static Map<String, Object> findSimpleResult(String sql, List<Object> params) throws SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        int index = 1;
        //pstmt = connection.prepareStatement(sql);
        pstmt = dataSource.getConnection().prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();//返回查询结果  
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();
        while (resultSet.next()) {
            for (int i = 0; i < col_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
        }
        return map;
    }

    /**
     * 查询多条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> findModeResult(String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int index = 1;
        // pstmt = connection.prepareStatement(sql);
        pstmt = dataSource.getConnection().prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }

        return list;
    }

    /**
     * 通过反射机制查询单条记录
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public static <T> T findSimpleRefResult(String sql, List<Object> params,
                                            Class<T> cls) throws Exception {
        T resultObject = null;
        int index = 1;
        //pstmt = connection.prepareStatement(sql);
        pstmt = dataSource.getConnection().prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            //通过反射机制创建一个实例  
            resultObject = cls.newInstance();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                Field field = cls.getDeclaredField(cols_name);
                field.setAccessible(true); //打开javabean的访问权限  
                field.set(resultObject, cols_value);
            }
        }
        return resultObject;

    }

    /**
     * 通过反射机制查询多条记录
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public static <T> List<T> findMoreRefResult(String sql, List<Object> params,
                                                Class<T> cls) throws Exception {
        List<T> list = new ArrayList<T>();
        int index = 1;
        //pstmt = connection.prepareStatement(sql);
        pstmt = dataSource.getConnection().prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            //通过反射机制创建一个实例  
            T resultObject = cls.newInstance();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                Field field = cls.getDeclaredField(cols_name);
                field.setAccessible(true); //打开javabean的访问权限  
                field.set(resultObject, cols_value);
            }
            list.add(resultObject);
        }
        return list;
    }

    /**
     * 释放数据库连接
     */
    public static void releaseConn() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @param args
     */
    // public static void main(String[] args) throws SQLException {
    // TODO Auto-generated method stub
    //     JdbcUtils jdbcUtils = new JdbcUtils();
    //    jdbcUtils.getConnection();

    /*******************增*********************/
        /*      String sql = "insert into userinfo (username, pswd) values (?, ?), (?, ?), (?, ?)"; 
        List<Object> params = new ArrayList<Object>(); 
        params.add("小明"); 
        params.add("123xiaoming"); 
        params.add("张三"); 
        params.add("zhangsan"); 
        params.add("李四"); 
        params.add("lisi000"); 
        try { 
            boolean flag = jdbcUtils.updateByPreparedStatement(sql, params); 
            System.out.println(flag); 
        } catch (SQLException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        }*/


/*******************删*********************/
//删除名字为张三的记录  
/*      String sql = "delete from userinfo where username = ?"; 
List<Object> params = new ArrayList<Object>(); 
params.add("小明"); 
boolean flag = jdbcUtils.updateByPreparedStatement(sql, params);*/

/*******************改*********************/
//将名字为李四的密码改了  
/*      String sql = "update userinfo set pswd = ? where username = ? "; 
List<Object> params = new ArrayList<Object>(); 
params.add("lisi88888"); 
params.add("李四"); 
boolean flag = jdbcUtils.updateByPreparedStatement(sql, params); 
System.out.println(flag);*/

/*******************查*********************/
//不利用反射查询多个记录  
/*      String sql2 = "select * from userinfo "; 
List<Map<String, Object>> list = jdbcUtils.findModeResult(sql2, null); 
System.out.println(list);*/

//利用反射查询 单条记录  

//        String sql = "select * from userinfo where username = ? ";
//        List<Object> params = new ArrayList<Object>();
//        params.add("李四");
//        UserInfo userInfo;
//        try {
//            userInfo = jdbcUtils.findSimpleRefResult(sql, params, UserInfo.class);
//            System.out.print(userInfo);
//        } catch (Exception e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }


    //   }
}
